/*
 * Created on 20.11.2003
 * Local Projects Database
 * Mihai Postelnicu (mihai@ro-gateway.org)
 * (c) 2003 by eRomania Gateway
 */
package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.DBWrapable;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.Country;


/**
 * @author Mihai Postelnicu
 *         <p/>
 *         e-mail (mihai@ro-gateway.org) (c) 2003 by eRomania Gateway
 */
public class CountryDB extends DBPersistenceManager implements DBWrapable {
     private CountryDB( PersistenceToken token ) {
          super( token );
     }

     public static CountryDB getManager( PersistenceToken token ) {
          return new CountryDB( token );
     }

     public Object get( Object o ) throws Exception {
          Country m = ( Country ) o;

          if ( m.getIso3() != null )
               return getByISO3( m );

          //default
          return getCountries();
     }

     public void synchronize( Object o ) throws SQLException {
          //abstract default implementation;
     }

     public void delete( Object o ) throws SQLException {
          //abstract default implementation;
     }

     public ArrayList getCountries() throws SQLException {
          ArrayList ret = new ArrayList();
          Connection con = getConnection();

          try {
               PreparedStatement ps = con.prepareStatement( countryList );
               ResultSet rs = ps.executeQuery();

               while ( rs.next() ) {
                    Country oc = new Country( rs.getString( 1 ), rs.getString( 2 ) );
                    ret.add( oc );
               }

               rs.close();
               ps.close();
          } catch ( SQLException ex ) {
               throw ex;
          } finally {
               con.close();
          }

          return ret;
     }

     public Country getByISO3( Country o ) throws SQLException {
          Connection con = getConnection();

          Country oc = null;
          try {
               PreparedStatement ps = con.prepareStatement( get_By_ISO3 );
               ps.setString( 1, o.getIso3() );

               ResultSet rs = ps.executeQuery();

               if ( rs.next() )
                    oc = new Country( rs.getString( 1 ), rs.getString( 2 ) );

               rs.close();
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }

          return oc;
     }

     public Country getByISO3( String iso3 ) throws SQLException {
          Connection con = getConnection();
          Country oc = null;

          try {
               PreparedStatement ps = con.prepareStatement( get_By_ISO3 );
               ps.setString( 1, iso3 );

               ResultSet rs = ps.executeQuery();

               if ( rs.next() )
                    oc = new Country( rs.getString( 1 ), rs.getString( 2 ) );

               rs.close();
               ps.close();
          } catch ( SQLException sqlEx ) {
               throw sqlEx;
          } finally {
               con.close();
          }

          return oc;
     }

     private static final String countryList = "select name,iso3 from countries_iso3 order by name";
     private static final String get_By_ISO3 = "select name,iso3 from countries_iso3 where iso3=?";
}
